package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.telecom.cms.dto.CampCustAdvInfo;
import com.telecom.cms.dto.CampCustAdvInfoEx;
import com.telecom.cms.dto.TwoId;
import com.telecom.cms.util.Constant;

public class CampCustAdvInfoDAO {
	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampCustAdvInfo transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".Camp_Cust_Adv_Info where camp_id="
				+ transientInstance.getCampId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Cust_Adv_Info set latn_id='"
						+ transientInstance.getLatnId() + "',camp_id='"
						+ transientInstance.getCampId() + "',camp_seq_id='"
						+ transientInstance.getCampSeqId() + "',cust_id='"
						+ transientInstance.getCustId() + "',prd_inst_id='"
						+ transientInstance.getPrdInstId() + "',camp_adv_id='"
						+ transientInstance.getCampAdv() + "',comm_time="
						+ transientInstance.getCommTime() + " where camp_id="
						+ transientInstance.getCampId();
			else
				sql = "insert into " + db + ".Camp_Cust_Adv_Info values('"
						+ transientInstance.getLatnId() + "' , '"
						+ transientInstance.getCampId() + "' , '"
						+ transientInstance.getCampSeqId() + "' , '"

						+ transientInstance.getCustId() + "' , '"
						+ transientInstance.getPrdInstId() + "' , '"

						+ transientInstance.getCampAdv() + "' , '"

						+ transientInstance.getCommTime() + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(CampCustAdvInfo persistentInstance) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Adv_Info where camp_Id = "
				+ persistentInstance.getCampId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	public void delete(String campid,String campseqid) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Adv_Info where camp_Id = "
				+ campid+" and camp_seq_id="+campseqid;

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampCustAdvInfo findById(String id) {

		String sql = "select * from " + db
				+ ".Camp_Cust_Adv_Info where Camp_Id = " + id;
		CampCustAdvInfo sd = new CampCustAdvInfo();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {

				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));

				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setCampAdv(rs.getString(6));
				sd.setCommTime(rs.getString(7));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Cust_Adv_Info";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustAdvInfo sd = new CampCustAdvInfo();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));

				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setCampAdv(rs.getString(6));
				sd.setCommTime(rs.getString(7));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAll(String campid,String campseqid,String prdinstid,String custid) {

		String sql = "select a.* from " + db + ".Camp_Cust_Adv_Info a " 
				    + "where a.camp_id="+campid
		    		+" and a.camp_seq_id="+campseqid+" and a.cust_id='"+custid+"' and a.prd_inst_id='"+prdinstid+"'";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				/*CampCustAdvInfoEx sd = new CampCustAdvInfoEx();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));

				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setCampAdv(rs.getString(6));
				sd.setCommTime(rs.getString(7));
				//sd.setCampAdvInfo(rs.getString(8));*/
				String [] actinfo=rs.getString(7).toLowerCase().split("<br>");
				for (int i=0;i<actinfo.length;i++)
					list.add(actinfo[i]);
				actinfo=rs.getString(6).toLowerCase().split("<br>");
				for (int i=0;i<actinfo.length;i++)
					list.add(actinfo[i]);	
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAllObj(String campid,String campseqid,String prdinstid,String custid) {

		String sql = "select a.* from " + db + ".Camp_Cust_Adv_Info a " 
				    + "where a.camp_id="+campid
		    		+" and a.camp_seq_id="+campseqid+" and a.cust_id='"+custid+"' and a.prd_inst_id='"+prdinstid+"'";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				
				String [] actinfo=rs.getString(7).toLowerCase().split("<br>");
				for (int i=0;i<actinfo.length;i++)
				{
					//list.add(actinfo[i]);
					CampCustAdvInfoEx sd = new CampCustAdvInfoEx();
					sd.setLatnId(rs.getString(1));
					sd.setCampId(rs.getInt(2));
					sd.setCampSeqId(rs.getInt(3));
	
					sd.setCustId(rs.getString(4));
					sd.setPrdInstId(rs.getString(5));
					sd.setCampAdv(actinfo[i]);
					//sd.setCommTime(rs.getString(7));
					list.add(sd);
					}
				actinfo=rs.getString(6).toLowerCase().split("<br>");
				for (int i=0;i<actinfo.length;i++)
				{
					//list.add(actinfo[i]);
					CampCustAdvInfoEx sd = new CampCustAdvInfoEx();
					sd.setLatnId(rs.getString(1));
					sd.setCampId(rs.getInt(2));
					sd.setCampSeqId(rs.getInt(3));
	
					sd.setCustId(rs.getString(4));
					sd.setPrdInstId(rs.getString(5));
					sd.setCampAdv(actinfo[i]);
					//sd.setCommTime(rs.getString(7));
					list.add(sd);
				}
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public HashMap findAllMap(String campid,String campseqid,List allidlist,String billingcycleid,int from)
	{
	     HashMap result =new HashMap();
	     TwoId twoid=null;
	     int preread=Integer.parseInt(com.telecom.cms.util.Constant.getStPara("CONT_PRE_READ"));
	     for (int i=from;i<allidlist.size()&&i<preread+from;i++)
	     {
	    	 twoid=(TwoId)allidlist.get(i);
	    	 twoid.setBillingcycleid(billingcycleid);
	    	 result.put(twoid, findAll(campid,campseqid,twoid.getPrdInstId(),twoid.getCustId()));
	     }
	     return result;
	}
	
	public void genAdvInfo(String campid,String campseqid,String advpara,String advcond,String latncd) {

		/*String sql = "insert into td_cms.CAMP_CUST_ADV_INFO "
			+"select latn_id,camp_id,camp_seq_id,prd_inst_id,cust_id,"+advid+",'����' "
			+" from td_cms.camp_cust_appd_rslt "
			+"where camp_id="+campid+" and camp_seq_id="+campseqid+" and user_id>0 "
			+"and prd_inst_id in( "
			+advcond+")";*/
		
		String sql=advcond.replace("{campid}", campid).replace("{campseqid}", campseqid).replace("{db}", db);
		String paras[]=advpara.split(",");
		System.out.println(sql);
		for(int i=0;i<paras.length;i++)
		{
		    String curpara[]=paras[i].split("=");
		    //if params include campseqid,but not equals input campseqid,abort exec;
		    if(curpara[0].equals("campseqid")&&!curpara[1].equals(campseqid))
		    	return;
		    if(curpara.length==2)
			   sql=sql.replace("{"+curpara[0]+"}", curpara[1]);
		}

		try {
			sql=(new Constant()).changeLatnId(latncd, sql);
			dao.insert(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}


}
